﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TVDL4.VO;

namespace TVDL4.DAO
{
    class GiaiDoanDAO
    {
        Random rnd = new Random();
        private dbConnection conn;
        
        public GiaiDoanDAO()
        {
            conn = new dbConnection();
        }
        /// <summary>
        /// tra ve mot thong tin vat lieu theo ten Mau vat lieu
        /// </summary>
        /// <param name="tenGiaiDoan"></param>
        /// <returns></returns>
        public DataTable SearchByName(string tenGiaiDoan)
        {
            string query = string.Format("select * from DMGiaiDoan a inner join DMCONGTRINH b on a.KEYSLCONGTRINH = b.KEYSL "
            + " where TenGiaiDoan like @tenGiaiDoan order by tencongtrinh");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@tenGiaiDoan", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(tenGiaiDoan);
            return conn.executeSelectQuery(query, sqlParameters);
        }

        /// <summary>
        /// tra ve mot thong tin GiaiDoan theo KEYSL
        /// </summary>
        /// <param name="tenGiaiDoan"></param>
        /// <returns></returns>
        public DataTable SearchByKEYSL(string keysl)
        {
            string query = string.Format("select * from DMGiaiDoan a inner join DMCONGTRINH b on a.KEYSLCONGTRINH = b.KEYSL "
            + " where a.KEYSL = @keysl");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(keysl);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        /// <summary>
        /// Get DMGiaiDoan_DETAIL theo keyslGiaiDoan 
        /// </summary>
        /// <param name="keySLGiaiDoan"></param>
        /// <returns></returns>
        public DataTable SearchGiaiDoanDetailByKeySLGiaiDoan(string keySLGiaiDoan)
        {
            string query = string.Format("select * from DMGiaiDoan_DETAIL where KeySLGiaiDoan = @keyslGiaiDoan");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keyslGiaiDoan", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(keySLGiaiDoan);
            return conn.executeSelectQuery(query, sqlParameters);
        }

        public DataTable SearchNguoiThucHienByKeySLGiaiDoan(string keySLGiaiDoan)
        {
            string query = string.Format("select * from DMGIAIDOAN_NGUOITHUCHIEN where KeySLGiaiDoan = @keyslGiaiDoan");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keyslGiaiDoan", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(keySLGiaiDoan);
            return conn.executeSelectQuery(query, sqlParameters);
        }

        /// <summary>
        /// lay tat ca cac du lieu tu table DMMAUVATLIEU
        /// </summary>
        /// <returns></returns>
        public DataTable SearchAll()
        {
            string query = string.Format("select * from DMGiaiDoan a inner join DMCONGTRINH b on a.KEYSLCONGTRINH = b.KEYSL order by thoigian,denthoigian,tencongtrinh");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        /// <summary>
        /// lay tat ca cac du lieu tu table DMMAUVATLIEU
        /// </summary>
        /// <returns></returns>
        public DataTable Search(string condition)
        {
            string query = string.Format("select * from DMGiaiDoan a inner join DMCONGTRINH b on a.KEYSLCONGTRINH = b.KEYSL where 1 = 1 " + condition + " order by thoigian,denthoigian,tencongtrinh");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        /// <summary>
        /// insert table DMTHINGHIEM
        /// </summary>
        /// <param name="tenVatLieu"></param>
        /// <returns></returns>
        public GiaiDoanVO InsertRecord(GiaiDoanVO GiaiDoanVO)
        {
            string keySL;
            string query = string.Format("Insert into DMGiaiDoan(KEYSL,TENGiaiDoan,HANGMUC,keyslcongtrinh,THOIGIAN,PHUTRACH,SLTHINGHIEM,SLTHAMCHIEU, "
            + " DAT_MT_NGUYENDANG,DAT_MT_ROI,DAT_MT_VATLIEU,DAT_MT_DOAM,"
            + " DAT_MB_NGUYENDANG,DAT_MB_ROI,DAT_MB_VATLIEU,DAT_MB_DOAM,"
            + " DAT_MKXB_NGUYENDANG,DAT_MKXB_ROI,DAT_MKXB_VATLIEU,DAT_MKXB_DOAM,"
            + " DA_MT_COLY, DA_MT_VALIEU,"
            + " DA_MB_COLY, DA_MB_VALIEU,"
            + " DA_MKXB_COLY, DA_MKXB_VALIEU,"
            + " CAT_MT_CATNEN,CAT_MT_VALIEU,"
            + " CAT_MB_CATNEN,CAT_MB_VALIEU,"
            + " CAT_MKXB_CATNEN,CAT_MKXB_VATLIEU,"
            + " NUOC_MT_BETONG, NUOC_MT_VISINH,"
            + " NUOC_MB_BETONG, NUOC_MB_VISINH,"
            + " NUOC_MKXB_BETONG,NUOC_MKXB_VISINH,"
            + " THACHHOC_MT,THACHHOC_MB,THACHHOC_MKXB,"
            + " THUYVAN_MT,THUYVAN_MB,THUYVAN_MKXB,"
            + " CONG_MT_TRONGGIO, CONG_MT_NGOAIGIO, CONG_MB_BOC, CONG_MB_ISO,CONG_NHANUOC,NSTHINGHIEM,NSTHAMCHIEU,NSBQ,DENTHOIGIAN,"
            + " DAT_MT_DAMCHAT,DAT_MB_DAMCHAT,DAT_MKXB_DAMCHAT) "
            + " VALUES (@keysl,@tenGiaiDoan,@hangmuc,@keyslcongtrinh,@thoigian,@phutrach,@slthinghiem,@slthamchieu,"
            + " @dat_mt_nguyendang,@dat_mt_roi,@dat_mt_vatlieu,@dat_mt_doam,"
            + " @dat_mb_nguyendang,@dat_mb_roi,@dat_mb_vatlieu,@dat_mb_doam,"
            + " @dat_mkxb_nguyendang,@dat_mkxb_roi,@dat_mkxb_vatlieu,@dat_mkxb_doam,"
            + " @da_mt_coly,@da_mt_vatlieu,"
            + " @da_mb_coly,@da_mb_vatlieu,"
            + " @da_mkxb_coly,@da_mkxb_vatlieu,"
            + " @cat_mt_catnen,@cat_mt_vatlieu,"
            + " @cat_mb_catnen,@cat_mb_vatlieu,"
            + " @cat_mkxb_catnen,@cat_mkxb_vatlieu,"
            + " @nuoc_mt_betong,@nuoc_mt_visinh,"
            + " @nuoc_mb_betong,@nuoc_mb_visinh,"
            + " @nuoc_mkxb_betong,@nuoc_mkxb_visinh,"
            + " @thachhoc_mt,@thachhoc_mb,@thachhoc_mkxb,"
            + " @thuyvan_mt,@thuyvan_mb,@thuyvan_mkxb,"
            + " @cong_mt_tronggio,@cong_mt_ngoaigio,@cong_mb_mauboc,@cong_mb_ISO,@cong_nhanuoc,@nsthinghiem,@nsthamchieu,@nsbq,@thoigianden,"
            + " @dat_mt_damchat,@dat_mb_damchat,@dat_mkxb_damchat)");


            SqlParameter[] sqlParameters = new SqlParameter[56];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(Module.CreateRunninNo(rnd));

            keySL = sqlParameters[0].Value.ToString();

            sqlParameters[1] = new SqlParameter("@tenGiaiDoan", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(GiaiDoanVO.TenGiaiDoan);

            sqlParameters[2] = new SqlParameter("@keyslcongtrinh", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(GiaiDoanVO.CongTrinh.KeySL);

            sqlParameters[3] = new SqlParameter("@phutrach", SqlDbType.NVarChar);
            sqlParameters[3].Value = Convert.ToString(GiaiDoanVO.PhuTrach);
            //dat          
            sqlParameters[4] = new SqlParameter("@dat_mt_nguyendang", SqlDbType.Int);
            sqlParameters[4].Value = Convert.ToString(GiaiDoanVO.Dat_MT_Nguyendang);

            sqlParameters[5] = new SqlParameter("@dat_mt_roi", SqlDbType.Int);
            sqlParameters[5].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Roi);
                        
            sqlParameters[6] = new SqlParameter("@dat_mt_vatlieu", SqlDbType.Int);
            sqlParameters[6].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Vatlieu );

            sqlParameters[7] = new SqlParameter("@dat_mt_doam", SqlDbType.Int);
            sqlParameters[7].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Doam);

            sqlParameters[8] = new SqlParameter("@dat_mb_nguyendang", SqlDbType.Int);
            sqlParameters[8].Value = Convert.ToString(GiaiDoanVO.Dat_MB_Nguyendang);

            sqlParameters[9] = new SqlParameter("@dat_mb_roi", SqlDbType.Int);
            sqlParameters[9].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Roi);
                        
            sqlParameters[10] = new SqlParameter("@dat_mb_vatlieu", SqlDbType.Int);
            sqlParameters[10].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Vatlieu );

            sqlParameters[11] = new SqlParameter("@dat_mb_doam", SqlDbType.Int);
            sqlParameters[11].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Doam);

            sqlParameters[12] = new SqlParameter("@dat_mkxb_nguyendang", SqlDbType.Int);
            sqlParameters[12].Value = Convert.ToString(GiaiDoanVO.Dat_MKXB_Nguyendang);

            sqlParameters[13] = new SqlParameter("@dat_mkxb_roi", SqlDbType.Int);
            sqlParameters[13].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Roi);
                        
            sqlParameters[14] = new SqlParameter("@dat_mkxb_vatlieu", SqlDbType.Int);
            sqlParameters[14].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Vatlieu );

            sqlParameters[15] = new SqlParameter("@dat_mkxb_doam", SqlDbType.Int);
            sqlParameters[15].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Doam);
            //dat
            sqlParameters[16] = new SqlParameter("@da_mt_coly", SqlDbType.Int);
            sqlParameters[16].Value = Convert.ToInt32(GiaiDoanVO.Da_MT_Coly);
                        
            sqlParameters[17] = new SqlParameter("@da_mt_vatlieu", SqlDbType.Int);
            sqlParameters[17].Value = Convert.ToInt32(GiaiDoanVO.Da_MT_Vatlieu );

            sqlParameters[18] = new SqlParameter("@da_mb_coly", SqlDbType.Int);
            sqlParameters[18].Value = Convert.ToInt32(GiaiDoanVO.Da_MB_Coly);
                        
            sqlParameters[19] = new SqlParameter("@da_mb_vatlieu", SqlDbType.Int);
            sqlParameters[19].Value = Convert.ToInt32(GiaiDoanVO.Da_MB_Vatlieu );
            
            sqlParameters[20] = new SqlParameter("@da_mkxb_coly", SqlDbType.Int);
            sqlParameters[20].Value = Convert.ToInt32(GiaiDoanVO.Da_MKXB_Coly);
                        
            sqlParameters[21] = new SqlParameter("@da_mkxb_vatlieu", SqlDbType.Int);
            sqlParameters[21].Value = Convert.ToInt32(GiaiDoanVO.Da_MKXB_Vatlieu );
            //cat            
            sqlParameters[22] = new SqlParameter("@cat_mt_catnen", SqlDbType.Int);
            sqlParameters[22].Value = Convert.ToInt32(GiaiDoanVO.Cat_MT_Catnen);
                        
            sqlParameters[23] = new SqlParameter("@cat_mt_vatlieu", SqlDbType.Int);
            sqlParameters[23].Value = Convert.ToInt32(GiaiDoanVO.Cat_MT_Vatlieu );

            sqlParameters[24] = new SqlParameter("@cat_mb_catnen", SqlDbType.Int);
            sqlParameters[24].Value = Convert.ToInt32(GiaiDoanVO.Cat_MB_Catnen);
                        
            sqlParameters[25] = new SqlParameter("@cat_mb_vatlieu", SqlDbType.Int);
            sqlParameters[25].Value = Convert.ToInt32(GiaiDoanVO.Cat_MB_Vatlieu );
            
            sqlParameters[26] = new SqlParameter("@cat_mkxb_catnen", SqlDbType.Int);
            sqlParameters[26].Value = Convert.ToInt32(GiaiDoanVO.Cat_MKXB_Catnen);
                        
            sqlParameters[27] = new SqlParameter("@cat_mkxb_vatlieu", SqlDbType.Int);
            sqlParameters[27].Value = Convert.ToInt32(GiaiDoanVO.Cat_MKXB_Vatlieu );
            //nuoc
            sqlParameters[28] = new SqlParameter("@nuoc_mt_betong", SqlDbType.Int);
            sqlParameters[28].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MT_Betong);
                        
            sqlParameters[29] = new SqlParameter("@nuoc_mt_visinh", SqlDbType.Int);
            sqlParameters[29].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MT_Visinh );

            sqlParameters[30] = new SqlParameter("@nuoc_mb_betong", SqlDbType.Int);
            sqlParameters[30].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MB_Betong);
                        
            sqlParameters[31] = new SqlParameter("@nuoc_mb_visinh", SqlDbType.Int);
            sqlParameters[31].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MB_Visinh );

            sqlParameters[32] = new SqlParameter("@nuoc_mkxb_betong", SqlDbType.Int);
            sqlParameters[32].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MKXB_Betong);
                        
            sqlParameters[33] = new SqlParameter("@nuoc_mkxb_visinh", SqlDbType.Int);
            sqlParameters[33].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MKXB_Visinh );
            //thach hoc
            sqlParameters[34] = new SqlParameter("@thachhoc_mt", SqlDbType.Int);
            sqlParameters[34].Value = Convert.ToInt32(GiaiDoanVO.Thachhoc_MT );

            sqlParameters[35] = new SqlParameter("@thachhoc_mb", SqlDbType.Int);
            sqlParameters[35].Value = Convert.ToInt32(GiaiDoanVO.Thachhoc_MB);
                        
            sqlParameters[36] = new SqlParameter("@thachhoc_mkxb", SqlDbType.Int);
            sqlParameters[36].Value = Convert.ToInt32(GiaiDoanVO.Thachhoc_MKXB );
            //thuy van
            sqlParameters[37] = new SqlParameter("@thuyvan_mt", SqlDbType.Int);
            sqlParameters[37].Value = Convert.ToInt32(GiaiDoanVO.Thuyvan_MT );

            sqlParameters[38] = new SqlParameter("@thuyvan_mb", SqlDbType.Int);
            sqlParameters[38].Value = Convert.ToInt32(GiaiDoanVO.Thuyvan_MB);
                        
            sqlParameters[39] = new SqlParameter("@thuyvan_mkxb", SqlDbType.Int);
            sqlParameters[39].Value = Convert.ToInt32(GiaiDoanVO.Thuyvan_MKXB );
            //cong
            sqlParameters[40] = new SqlParameter("@cong_mt_ngoaigio", SqlDbType.Float);
            sqlParameters[40].Value = Convert.ToInt32(GiaiDoanVO.Cong_MT_Ngoaigio);

            sqlParameters[41] = new SqlParameter("@cong_mt_tronggio", SqlDbType.Float);
            sqlParameters[41].Value = Convert.ToInt32(GiaiDoanVO.Cong_MT_Tronggio);

            sqlParameters[42] = new SqlParameter("@cong_mb_mauboc", SqlDbType.Float);
            sqlParameters[42].Value = Convert.ToInt32(GiaiDoanVO.Cong_MB_Boc);

            sqlParameters[43] = new SqlParameter("@cong_mb_ISO", SqlDbType.Float);
            sqlParameters[43].Value = Convert.ToInt32(GiaiDoanVO.Cong_MB_ISO);

            sqlParameters[44] = new SqlParameter("@cong_nhanuoc", SqlDbType.Float);
            sqlParameters[44].Value = Convert.ToInt32(GiaiDoanVO.Cong_Nhanuoc);

            sqlParameters[45] = new SqlParameter("@slthinghiem", SqlDbType.Int);
            sqlParameters[45].Value = Convert.ToInt32(GiaiDoanVO.SlThiNghiem);

            sqlParameters[46] = new SqlParameter("@slthamchieu", SqlDbType.Int);
            sqlParameters[46].Value = Convert.ToInt32(GiaiDoanVO.SlThamChieu);

            sqlParameters[47] = new SqlParameter("@thoigian", SqlDbType.NVarChar);
            sqlParameters[47].Value = Convert.ToString(GiaiDoanVO.ThoiGian);

            sqlParameters[48] = new SqlParameter("@nsthinghiem", SqlDbType.Float);
            sqlParameters[48].Value = Convert.ToString(GiaiDoanVO.NSThiNghiem);

            sqlParameters[49] = new SqlParameter("@nsthamchieu", SqlDbType.Float);
            sqlParameters[49].Value = Convert.ToString(GiaiDoanVO.NSThamChieu);

            sqlParameters[50] = new SqlParameter("@nsbq", SqlDbType.Float);
            sqlParameters[50].Value = Convert.ToString(GiaiDoanVO.NSBQ);

            sqlParameters[51] = new SqlParameter("@thoigianden", SqlDbType.NVarChar);
            sqlParameters[51].Value = Convert.ToString(GiaiDoanVO.ThoiGianDen);

            sqlParameters[52] = new SqlParameter("@dat_mt_damchat", SqlDbType.Int);
            sqlParameters[52].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Damchat);
           
            sqlParameters[53] = new SqlParameter("@dat_mb_damchat", SqlDbType.Int);
            sqlParameters[53].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Damchat);

            sqlParameters[54] = new SqlParameter("@dat_mkxb_damchat", SqlDbType.Int);
            sqlParameters[54].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Damchat);

            sqlParameters[55] = new SqlParameter("@hangmuc", SqlDbType.NVarChar);
            sqlParameters[55].Value =GiaiDoanVO.Hangmuc;
            
            if (conn.executeInsertQuery(query, sqlParameters))
            {
                GiaiDoanVO.KeySL = sqlParameters[0].Value.ToString();
                // INSET TABLE DMGIAIDOAN_DETAIL
                foreach (GiaiDoanDetailVO GiaiDoanDetailVO in GiaiDoanVO.LstGiaiDoanDetail)
                {
                    query = string.Format("Insert into DMGiaiDoan_DETAIL(KEYSL,KEYSLGiaiDoan,KEYSLTHINGHIEM,KEYSLMAUVATLIEU,KEYSLVATLIEU) "
                    + " VALUES (@keysl,@keyslGiaiDoan,@keyslthinghiem,@keyslmauvatlieu,@keyslvatlieu)");

                    sqlParameters = new SqlParameter[5];

                    sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
                    sqlParameters[0].Value = Convert.ToString(Module.CreateRunninNo(rnd));

                    sqlParameters[1] = new SqlParameter("@keyslGiaiDoan", SqlDbType.NVarChar);
                    sqlParameters[1].Value = Convert.ToString(keySL);

                    sqlParameters[2] = new SqlParameter("@keyslthinghiem", SqlDbType.NVarChar);
                    sqlParameters[2].Value = Convert.ToString(GiaiDoanDetailVO.KeySLThiNghiem);

                    sqlParameters[3] = new SqlParameter("@keyslmauvatlieu", SqlDbType.NVarChar);
                    sqlParameters[3].Value = Convert.ToString(GiaiDoanDetailVO.KeySLMauVatLieu);

                    sqlParameters[4] = new SqlParameter("@keyslvatlieu", SqlDbType.NVarChar);
                    sqlParameters[4].Value = Convert.ToString(GiaiDoanDetailVO.KeySLVatLieu);

                    conn.executeInsertQuery(query, sqlParameters);
                }
                foreach (GiaiDoanNguoiThucHienVO giaiDoanNguoiThucHien in GiaiDoanVO.LstGiaDoanNguoiThucHien)
                {
                    query = string.Format("Insert into DMGIAIDOAN_NGUOITHUCHIEN(KEYSL,KEYSLGiaiDoan,THUCHIEN,THINGHIEM,KETQUA) "
                    + " VALUES (@keysl,@keyslGiaiDoan,@thuchien,@thinghiem,@ketqua)");

                    sqlParameters = new SqlParameter[5];

                    sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
                    sqlParameters[0].Value = Convert.ToString(Module.CreateRunninNo(rnd));

                    sqlParameters[1] = new SqlParameter("@keyslGiaiDoan", SqlDbType.NVarChar);
                    sqlParameters[1].Value = Convert.ToString(keySL);

                    sqlParameters[2] = new SqlParameter("@thuchien", SqlDbType.NVarChar);
                    sqlParameters[2].Value = Convert.ToString(giaiDoanNguoiThucHien.ThucHien);

                    sqlParameters[3] = new SqlParameter("@thinghiem", SqlDbType.NVarChar);
                    sqlParameters[3].Value = Convert.ToString(giaiDoanNguoiThucHien.ThiNghiem);

                    sqlParameters[4] = new SqlParameter("@ketqua", SqlDbType.NVarChar);
                    sqlParameters[4].Value = Convert.ToString(giaiDoanNguoiThucHien.KetQua);

                    
                    conn.executeInsertQuery(query, sqlParameters);
                }

                return GiaiDoanVO;
            }
            else
            {
                return null;
            }

        }
        /// <summary>
        /// Update DMTHINGHIEM
        /// </summary>
        /// <param name="mauVatlieuVO"></param>
        /// <returns></returns>
        public bool UpdateRecord(GiaiDoanVO GiaiDoanVO)
        {
            string keySL;
            string query = string.Format("UPDATE DMGiaiDoan SET "
            + " TENGiaiDoan = @tenGiaiDoan,HANGMUC = @hangmuc,keyslCongTrinh = @keyslCongTrinh,PHUTRACH = @phutrach,THOIGIAN = @thoigian, "
            + " SLTHINGHIEM = @slthinghiem ,SLTHAMCHIEU = @slthamchieu, "
            + " DAT_MT_NGUYENDANG = @dat_mt_nguyendang ,DAT_MT_ROI = @dat_mt_roi ,DAT_MT_VATLIEU = @dat_mt_vatlieu ,DAT_MT_DOAM = @dat_mt_doam ,"
            + " DAT_MB_NGUYENDANG = @dat_mb_nguyendang,DAT_MB_ROI = @dat_mb_roi ,DAT_MB_VATLIEU=@dat_mb_vatlieu,DAT_MB_DOAM=@dat_mb_doam,"
            + " DAT_MKXB_NGUYENDANG=@dat_mkxb_nguyendang,DAT_MKXB_ROI=@dat_mkxb_roi,DAT_MKXB_VATLIEU=@dat_mkxb_vatlieu,DAT_MKXB_DOAM=@dat_mkxb_doam,"
            + " DA_MT_COLY=@da_mt_coly, DA_MT_VALIEU=@da_mt_vatlieu,"
            + " DA_MB_COLY=@da_mb_coly, DA_MB_VALIEU=@da_mb_vatlieu,"
            + " DA_MKXB_COLY= @da_mkxb_coly, DA_MKXB_VALIEU=@da_mkxb_vatlieu,"
            + " CAT_MT_CATNEN=@cat_mt_catnen,CAT_MT_VALIEU=@cat_mt_vatlieu,"
            + " CAT_MB_CATNEN= @cat_mb_catnen,CAT_MB_VALIEU=@cat_mb_vatlieu,"
            + " CAT_MKXB_CATNEN=@cat_mkxb_catnen,CAT_MKXB_VATLIEU=@cat_mkxb_catnen,"
            + " NUOC_MT_BETONG=@nuoc_mt_betong, NUOC_MT_VISINH=@nuoc_mt_visinh,"
            + " NUOC_MB_BETONG=@nuoc_mb_betong, NUOC_MB_VISINH=@nuoc_mb_visinh,"
            + " NUOC_MKXB_BETONG=@nuoc_mkxb_betong,NUOC_MKXB_VISINH=@nuoc_mb_visinh,"
            + " THACHHOC_MT=@thachhoc_mt ,THACHHOC_MB=@thachhoc_mb,THACHHOC_MKXB=@thachhoc_mkxb,"
            + " THUYVAN_MT=@thuyvan_mt,THUYVAN_MB=@thuyvan_mb,THUYVAN_MKXB=@thuyvan_mkxb,"
            + " CONG_MT_TRONGGIO= @cong_mt_tronggio, CONG_MT_NGOAIGIO=@cong_mt_ngoaigio, CONG_MB_BOC=@cong_mb_mauboc, CONG_MB_ISO=@cong_mb_ISO,CONG_NHANUOC=@cong_nhanuoc,"
            + " NSTHINGHIEM = @nsthinghiem, NSTHAMCHIEU = @nsthamchieu, NSBQ = @nsbq,DENTHOIGIAN = @thoigianden, "
            + " DAT_MT_DAMCHAT = @dat_mt_damchat, DAT_MB_DAMCHAT = @dat_mb_damchat, DAT_MKXB_DAMCHAT = @dat_mkxb_damchat "
            + " WHERE KEYSL = @keysl");
            

            SqlParameter[] sqlParameters = new SqlParameter[56];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(GiaiDoanVO.KeySL);

            keySL = sqlParameters[0].Value.ToString();

            sqlParameters[1] = new SqlParameter("@tenGiaiDoan", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(GiaiDoanVO.TenGiaiDoan);

            sqlParameters[2] = new SqlParameter("@keyslcongtrinh", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(GiaiDoanVO.CongTrinh.KeySL);

            sqlParameters[3] = new SqlParameter("@phutrach", SqlDbType.NVarChar);
            sqlParameters[3].Value = Convert.ToString(GiaiDoanVO.PhuTrach);
            //dat          
            sqlParameters[4] = new SqlParameter("@dat_mt_nguyendang", SqlDbType.Int);
            sqlParameters[4].Value = Convert.ToString(GiaiDoanVO.Dat_MT_Nguyendang);

            sqlParameters[5] = new SqlParameter("@dat_mt_roi", SqlDbType.Int);
            sqlParameters[5].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Roi);
                        
            sqlParameters[6] = new SqlParameter("@dat_mt_vatlieu", SqlDbType.Int);
            sqlParameters[6].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Vatlieu );

            sqlParameters[7] = new SqlParameter("@dat_mt_doam", SqlDbType.Int);
            sqlParameters[7].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Doam);

            sqlParameters[8] = new SqlParameter("@dat_mb_nguyendang", SqlDbType.Int);
            sqlParameters[8].Value = Convert.ToString(GiaiDoanVO.Dat_MB_Nguyendang);

            sqlParameters[9] = new SqlParameter("@dat_mb_roi", SqlDbType.Int);
            sqlParameters[9].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Roi);
                        
            sqlParameters[10] = new SqlParameter("@dat_mb_vatlieu", SqlDbType.Int);
            sqlParameters[10].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Vatlieu );

            sqlParameters[11] = new SqlParameter("@dat_mb_doam", SqlDbType.Int);
            sqlParameters[11].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Doam);

            sqlParameters[12] = new SqlParameter("@dat_mkxb_nguyendang", SqlDbType.Int);
            sqlParameters[12].Value = Convert.ToString(GiaiDoanVO.Dat_MKXB_Nguyendang);

            sqlParameters[13] = new SqlParameter("@dat_mkxb_roi", SqlDbType.Int);
            sqlParameters[13].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Roi);
                        
            sqlParameters[14] = new SqlParameter("@dat_mkxb_vatlieu", SqlDbType.Int);
            sqlParameters[14].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Vatlieu );

            sqlParameters[15] = new SqlParameter("@dat_mkxb_doam", SqlDbType.Int);
            sqlParameters[15].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Doam);
            //dat
            sqlParameters[16] = new SqlParameter("@da_mt_coly", SqlDbType.Int);
            sqlParameters[16].Value = Convert.ToInt32(GiaiDoanVO.Da_MT_Coly);
                        
            sqlParameters[17] = new SqlParameter("@da_mt_vatlieu", SqlDbType.Int);
            sqlParameters[17].Value = Convert.ToInt32(GiaiDoanVO.Da_MT_Vatlieu );

            sqlParameters[18] = new SqlParameter("@da_mb_coly", SqlDbType.Int);
            sqlParameters[18].Value = Convert.ToInt32(GiaiDoanVO.Da_MB_Coly);
                        
            sqlParameters[19] = new SqlParameter("@da_mb_vatlieu", SqlDbType.Int);
            sqlParameters[19].Value = Convert.ToInt32(GiaiDoanVO.Da_MB_Vatlieu );
            
            sqlParameters[20] = new SqlParameter("@da_mkxb_coly", SqlDbType.Int);
            sqlParameters[20].Value = Convert.ToInt32(GiaiDoanVO.Da_MKXB_Coly);
                        
            sqlParameters[21] = new SqlParameter("@da_mkxb_vatlieu", SqlDbType.Int);
            sqlParameters[21].Value = Convert.ToInt32(GiaiDoanVO.Da_MKXB_Vatlieu );
            //cat            
            sqlParameters[22] = new SqlParameter("@cat_mt_catnen", SqlDbType.Int);
            sqlParameters[22].Value = Convert.ToInt32(GiaiDoanVO.Cat_MT_Catnen);
                        
            sqlParameters[23] = new SqlParameter("@cat_mt_vatlieu", SqlDbType.Int);
            sqlParameters[23].Value = Convert.ToInt32(GiaiDoanVO.Cat_MT_Vatlieu );

            sqlParameters[24] = new SqlParameter("@cat_mb_catnen", SqlDbType.Int);
            sqlParameters[24].Value = Convert.ToInt32(GiaiDoanVO.Cat_MB_Catnen);
                        
            sqlParameters[25] = new SqlParameter("@cat_mb_vatlieu", SqlDbType.Int);
            sqlParameters[25].Value = Convert.ToInt32(GiaiDoanVO.Cat_MB_Vatlieu );
            
            sqlParameters[26] = new SqlParameter("@cat_mkxb_catnen", SqlDbType.Int);
            sqlParameters[26].Value = Convert.ToInt32(GiaiDoanVO.Cat_MKXB_Catnen);
                        
            sqlParameters[27] = new SqlParameter("@cat_mkxb_vatlieu", SqlDbType.Int);
            sqlParameters[27].Value = Convert.ToInt32(GiaiDoanVO.Cat_MKXB_Vatlieu );
            //nuoc
            sqlParameters[28] = new SqlParameter("@nuoc_mt_betong", SqlDbType.Int);
            sqlParameters[28].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MT_Betong);
                        
            sqlParameters[29] = new SqlParameter("@nuoc_mt_visinh", SqlDbType.Int);
            sqlParameters[29].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MT_Visinh );

            sqlParameters[30] = new SqlParameter("@nuoc_mb_betong", SqlDbType.Int);
            sqlParameters[30].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MB_Betong);
                        
            sqlParameters[31] = new SqlParameter("@nuoc_mb_visinh", SqlDbType.Int);
            sqlParameters[31].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MB_Visinh );

            sqlParameters[32] = new SqlParameter("@nuoc_mkxb_betong", SqlDbType.Int);
            sqlParameters[32].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MKXB_Betong);
                        
            sqlParameters[33] = new SqlParameter("@nuoc_mkxb_visinh", SqlDbType.Int);
            sqlParameters[33].Value = Convert.ToInt32(GiaiDoanVO.Nuoc_MKXB_Visinh );
            //thach hoc
            sqlParameters[34] = new SqlParameter("@thachhoc_mt", SqlDbType.Int);
            sqlParameters[34].Value = Convert.ToInt32(GiaiDoanVO.Thachhoc_MT );

            sqlParameters[35] = new SqlParameter("@thachhoc_mb", SqlDbType.Int);
            sqlParameters[35].Value = Convert.ToInt32(GiaiDoanVO.Thachhoc_MB);
                        
            sqlParameters[36] = new SqlParameter("@thachhoc_mkxb", SqlDbType.Int);
            sqlParameters[36].Value = Convert.ToInt32(GiaiDoanVO.Thachhoc_MKXB );
            //thuy van
            sqlParameters[37] = new SqlParameter("@thuyvan_mt", SqlDbType.Int);
            sqlParameters[37].Value = Convert.ToInt32(GiaiDoanVO.Thuyvan_MT );

            sqlParameters[38] = new SqlParameter("@thuyvan_mb", SqlDbType.Int);
            sqlParameters[38].Value = Convert.ToInt32(GiaiDoanVO.Thuyvan_MB);
                        
            sqlParameters[39] = new SqlParameter("@thuyvan_mkxb", SqlDbType.Int);
            sqlParameters[39].Value = Convert.ToInt32(GiaiDoanVO.Thuyvan_MKXB );
            //cong
            sqlParameters[40] = new SqlParameter("@cong_mt_ngoaigio", SqlDbType.Float);
            sqlParameters[40].Value = Convert.ToInt32(GiaiDoanVO.Cong_MT_Ngoaigio);

            sqlParameters[41] = new SqlParameter("@cong_mt_tronggio", SqlDbType.Float);
            sqlParameters[41].Value = Convert.ToInt32(GiaiDoanVO.Cong_MT_Tronggio);

            sqlParameters[42] = new SqlParameter("@cong_mb_mauboc", SqlDbType.Float);
            sqlParameters[42].Value = Convert.ToInt32(GiaiDoanVO.Cong_MB_Boc);

            sqlParameters[43] = new SqlParameter("@cong_mb_ISO", SqlDbType.Float);
            sqlParameters[43].Value = Convert.ToInt32(GiaiDoanVO.Cong_MB_ISO);

            sqlParameters[44] = new SqlParameter("@cong_nhanuoc", SqlDbType.Float);
            sqlParameters[44].Value = Convert.ToInt32(GiaiDoanVO.Cong_Nhanuoc);

            sqlParameters[45] = new SqlParameter("@slthinghiem", SqlDbType.Int);
            sqlParameters[45].Value = Convert.ToInt32(GiaiDoanVO.SlThiNghiem);

            sqlParameters[46] = new SqlParameter("@slthamchieu", SqlDbType.Int);
            sqlParameters[46].Value = Convert.ToInt32(GiaiDoanVO.SlThamChieu);

            sqlParameters[47] = new SqlParameter("@thoigian", SqlDbType.NVarChar);
            sqlParameters[47].Value = Convert.ToString(GiaiDoanVO.ThoiGian);

            sqlParameters[48] = new SqlParameter("@nsthinghiem", SqlDbType.Float);
            sqlParameters[48].Value = Convert.ToString(GiaiDoanVO.NSThiNghiem);

            sqlParameters[49] = new SqlParameter("@nsthamchieu", SqlDbType.Float);
            sqlParameters[49].Value = Convert.ToString(GiaiDoanVO.NSThamChieu);

            sqlParameters[50] = new SqlParameter("@nsbq", SqlDbType.Float);
            sqlParameters[50].Value = Convert.ToString(GiaiDoanVO.NSBQ);

            sqlParameters[51] = new SqlParameter("@thoigianden", SqlDbType.NVarChar);
            sqlParameters[51].Value = Convert.ToString(GiaiDoanVO.ThoiGianDen);

            sqlParameters[52] = new SqlParameter("@dat_mt_damchat", SqlDbType.Int);
            sqlParameters[52].Value = Convert.ToInt32(GiaiDoanVO.Dat_MT_Damchat);

            sqlParameters[53] = new SqlParameter("@dat_mb_damchat", SqlDbType.Int);
            sqlParameters[53].Value = Convert.ToInt32(GiaiDoanVO.Dat_MB_Damchat);

            sqlParameters[54] = new SqlParameter("@dat_mkxb_damchat", SqlDbType.Int);
            sqlParameters[54].Value = Convert.ToInt32(GiaiDoanVO.Dat_MKXB_Damchat);

            sqlParameters[55] = new SqlParameter("@hangmuc", SqlDbType.NVarChar);
            sqlParameters[55].Value = GiaiDoanVO.Hangmuc;

            conn.executeInsertQuery(query, sqlParameters);

            query = string.Format("Delete DMGiaiDoan_DETAIL Where KEYSLGiaiDoan = @keysl");
            sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(GiaiDoanVO.KeySL);
            conn.executeInsertQuery(query, sqlParameters);

            foreach (GiaiDoanDetailVO GiaiDoanDetailVO in GiaiDoanVO.LstGiaiDoanDetail)
            {
                query = string.Format("Insert into DMGiaiDoan_DETAIL(KEYSL,KEYSLGiaiDoan,KEYSLTHINGHIEM,KEYSLMAUVATLIEU,KEYSLVATLIEU) "
                + " VALUES (@keysl,@keyslGiaiDoan,@keyslthinghiem,@keyslmauvatlieu,@keyslvatlieu)");

                sqlParameters = new SqlParameter[5];

                sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
                sqlParameters[0].Value = Convert.ToString(Module.CreateRunninNo(rnd));

                sqlParameters[1] = new SqlParameter("@keyslGiaiDoan", SqlDbType.NVarChar);
                sqlParameters[1].Value = Convert.ToString(keySL);

                sqlParameters[2] = new SqlParameter("@keyslthinghiem", SqlDbType.NVarChar);
                sqlParameters[2].Value = Convert.ToString(GiaiDoanDetailVO.KeySLThiNghiem);

                sqlParameters[3] = new SqlParameter("@keyslmauvatlieu", SqlDbType.NVarChar);
                sqlParameters[3].Value = Convert.ToString(GiaiDoanDetailVO.KeySLMauVatLieu);

                sqlParameters[4] = new SqlParameter("@keyslvatlieu", SqlDbType.NVarChar);
                sqlParameters[4].Value = Convert.ToString(GiaiDoanDetailVO.KeySLVatLieu);

                conn.executeInsertQuery(query, sqlParameters);
            }
            query = string.Format("Delete DMGIAIDOAN_NGUOITHUCHIEN Where KEYSLGiaiDoan = @keysl");
            sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(GiaiDoanVO.KeySL);
            conn.executeInsertQuery(query, sqlParameters);

            foreach (GiaiDoanNguoiThucHienVO giaiDoanNguoiThucHien in GiaiDoanVO.LstGiaDoanNguoiThucHien)
            {
                query = string.Format("Insert into DMGIAIDOAN_NGUOITHUCHIEN(KEYSL,KEYSLGiaiDoan,THUCHIEN,THINGHIEM,KETQUA) "
                + " VALUES (@keysl,@keyslGiaiDoan,@thuchien,@thinghiem,@ketqua)");

                sqlParameters = new SqlParameter[5];

                sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
                sqlParameters[0].Value = Convert.ToString(Module.CreateRunninNo(rnd));

                sqlParameters[1] = new SqlParameter("@keyslGiaiDoan", SqlDbType.NVarChar);
                sqlParameters[1].Value = Convert.ToString(keySL);

                sqlParameters[2] = new SqlParameter("@thuchien", SqlDbType.NVarChar);
                sqlParameters[2].Value = Convert.ToString(giaiDoanNguoiThucHien.ThucHien);

                sqlParameters[3] = new SqlParameter("@thinghiem", SqlDbType.NVarChar);
                sqlParameters[3].Value = Convert.ToString(giaiDoanNguoiThucHien.ThiNghiem);

                sqlParameters[4] = new SqlParameter("@ketqua", SqlDbType.NVarChar);
                sqlParameters[4].Value = Convert.ToString(giaiDoanNguoiThucHien.KetQua);


                conn.executeInsertQuery(query, sqlParameters);
            }
            return true;
        }
        /// <summary>
        /// Xóa DMTHINGHIEM
        /// </summary>
        /// <param name="keySL"></param>
        /// <returns></returns>
        public bool DeleteRecord(string keySL)
        {
            string query;
            query = string.Format("Delete DMGIAIDOAN_DETAIL Where KEYSLGIAIDOAN = @keysl");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(keySL);
            conn.executeInsertQuery(query, sqlParameters);

            query = string.Format("Delete DMGIAIDOAN_NGUOITHUCHIEN Where KEYSLGIAIDOAN = @keysl");
            sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(keySL);
            conn.executeInsertQuery(query, sqlParameters);

            query = string.Format("Delete DMGiaiDoan Where KEYSL = @keysl");
            sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(keySL);
            return conn.executeInsertQuery(query, sqlParameters);
            

        }
        public DataTable CheckGiaiDoan(string giandoan, string congtrinh,string hangmuc)
        {
            string query = string.Format("select * from dbo.DMGIAIDOAN where tengiaidoan = @tengiaidoan and keyslcongtrinh = @keyslcongtrinh and hangmuc = @hangmuc");
            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@tengiaidoan", SqlDbType.NVarChar);
            sqlParameters[0].Value =giandoan;
            sqlParameters[1] = new SqlParameter("@keyslcongtrinh", SqlDbType.NVarChar);
            sqlParameters[1].Value = congtrinh;
            sqlParameters[2] = new SqlParameter("@hangmuc", SqlDbType.NVarChar);
            sqlParameters[2].Value = hangmuc;
            return conn.executeSelectQuery(query, sqlParameters);
        }
        
    }
}
